﻿using Infrastructure.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SQLite;
using Dapper;
using Infrastructure.Util;

namespace Infrastructure.DAL
{
    public class UserDAL
    {
        /// <summary>
        /// 查询所有用户
        /// </summary>
        /// <returns></returns>
        public List<Users> GetAll()
        {
            using (IDbConnection conn = 
                new SQLiteConnection(DataBaseHelper.ConnnectionString())) {
               return  conn.Query<Users>("select * from Users").ToList();
            }
        }
        /// <summary>
        /// 新增用户
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public int Insert(Users user)
        {
            using (IDbConnection conn =
                new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                return conn.Execute("insert into Users([name],account,password,create_time) values(@name,@account,@password,@create_time)",user);
            }
        }
        /// <summary>
        /// 删除用户
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            using (IDbConnection conn =
                new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                return conn.Execute("delete from Users where id=@id", new { id=id});
            }
        }

        public Users GetByAccount(string account)
        {
            using (IDbConnection conn =
              new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                return conn.Query<Users>("select id,name,account,password,create_time from Users where account=@account",
                    new { account = account, }).FirstOrDefault();
            }
        }
        /// <summary>
        /// 修改密码
        /// </summary>
        /// <param name="currentUser"></param>
        /// <returns></returns>
        public int UpdatePwd(Users currentUser)
        {
            using (IDbConnection conn =
             new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                return conn.Execute("update Users set password=@password  where id=@id;",
                    currentUser);
            }
        }

        /// <summary>
        /// 根据账号和密码查询用户
        /// </summary>
        /// <param name="name"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public Users GetByUserNameAndPwd(string name,string pwd)
        {
            using (IDbConnection conn =
               new SQLiteConnection(DataBaseHelper.ConnnectionString()))
            {
                return conn.Query<Users>("select id,name,account,password,create_time from Users where account=@account and password=@pwd", 
                    new { account = name,pwd=pwd }).FirstOrDefault();
            }
        }
    }
}
